# utils/leadnews_wemedia_db_util.py
import pymysql



def get_connection():
    return pymysql.connect(
        host='121.5.37.61',     # 修改为你的数据库地址
        port=3306,
        user='root',
        password='xsroot',
        database='leadnews_wemedia',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )


def query_article_title_by_keyword(keyword):
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                "SELECT title FROM wm_news WHERE title LIKE %s",
                (f'%{keyword}%',)
            )
            row = cursor.fetchone()
            return (row['title']) if row else None
    finally:
        conn.close()

def query_article_title_by_channel(channel):
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            sql = """
                        SELECT title FROM wm_news
                        WHERE channel_id = %s LIMIT 1
                        """
            cursor.execute(sql, (channel,))
            row = cursor.fetchone()
            return row['title'] if row else None
    finally:
        conn.close()
def query_article_title_by_user(username):
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            sql = """
            SELECT title,status FROM wm_news
            WHERE user_id = (
                SELECT id FROM wm_user WHERE name = %s
            )
            ORDER BY created_time DESC LIMIT 1
            """
            cursor.execute(sql, (username,))
            row = cursor.fetchone()
            return (row['title'], row['status']) if row else (None, None)
    finally:
        conn.close()

def query_article_title_by_user_and_code(username,code):
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            sql = """
            SELECT title FROM wm_news
            WHERE user_id = (
                SELECT id FROM wm_user WHERE name = %s
            )
            AND status = %s
            ORDER BY created_time DESC LIMIT 1
            """
            cursor.execute(sql, (username,code))
            row = cursor.fetchone()
            return row['title'] if row else None
    finally:
        conn.close()

def query_titles_by_page(username,page_num, page_size=10,need = 1):
    conn = get_connection()
    offset = (page_num - 1) * page_size
    try:
        with conn.cursor() as cursor:
            sql = """
            SELECT title FROM wm_news
            WHERE user_id = (
                SELECT id FROM wm_user WHERE name = %s
            )
            ORDER BY created_time DESC LIMIT %s, %s
            """
            cursor.execute(sql, (username,offset,need))
            row = cursor.fetchone()
            return row['title'] if row else None
    finally:
        conn.close()

def query_total_titles(username):
    conn = get_connection()

    try:
        with conn.cursor() as cursor:
            sql = """
            SELECT COUNT(*) total FROM wm_news
            WHERE user_id = (
                SELECT id FROM wm_user WHERE name = %s
            )
            """
            cursor.execute(sql, (username,))
            row = cursor.fetchone()
            print(row)
            return row['total'] if row else None
    finally:
        conn.close()

def query_news(username, code = None, keyword= None, channel= None):
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            # 查询满足条件的总条数
            count_sql = """
                    SELECT COUNT(*) total
                    FROM wm_news
                    WHERE (user_id = (SELECT id FROM wm_user WHERE name = %s) OR %s IS NULL)
                      AND (status = %s OR %s IS NULL)
                      AND (title LIKE CONCAT('%%', %s, '%%') OR %s = '' OR %s IS NULL)
                      AND (channel_id = %s OR %s IS NULL)
                    """
            cursor.execute(count_sql, (username, username,
                                       code, code,
                                       keyword, keyword, keyword,
                                       channel, channel))
            row_count = cursor.fetchone()
            total_counts = row_count['total'] if row_count else 0

            # 查询第一条数据
            data_sql = """
                    SELECT title
                    FROM wm_news
                    WHERE (user_id = (SELECT id FROM wm_user WHERE name = %s) OR %s IS NULL)
                      AND (status = %s OR %s IS NULL)
                      AND (title LIKE CONCAT('%%', %s, '%%') OR %s = '' OR %s IS NULL)
                      AND (channel_id = %s OR %s IS NULL)
                    ORDER BY created_time DESC
                    LIMIT 1
                    """
            cursor.execute(data_sql, (username, username,
                                      code, code,
                                      keyword, keyword, keyword,
                                      channel, channel))
            row = cursor.fetchone()
            first_title = row['title'] if row else None

            return total_counts, first_title
    finally:
        conn.close()



# print(query_article_title_by_user_and_code("admin",0))
# print(query_titles_by_page("admin",2,10,1))
# print(query_total_titles("admin"))
# print(query_article_title_by_channel("java"))
# print(query_news("admin"))
# print(query_news("admin"))
# print(query_news("admin",9,"山东","1"))
# print(query_news("admin",9,"山东","1"))